﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Data;

namespace Howork
{
    public class QjRecord
    {
        public string emp_name { get; set; }
        public string department { get; set; }
        public string begin_time { get; set; }
        public string end_time { get; set; }
        public string reason { get; set; }
    }

    public class DBAdapter
    {
        private string strConn = "Driver={SQL Server Native Client 10.0};Server=localhost;Database=howork_c;Uid=Yuleo;Pwd=yuleoyubin";
        private OdbcConnection connection;
        
        public DBAdapter()
        {
            connection = new OdbcConnection(strConn);
        }

        public void Close()
        {
            connection.Close();
        }

        public List<RoomInfo> getRoomListAll()
        {
            string strQuery = "select * from room";
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            List<RoomInfo> roomList = new List<RoomInfo>();
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                int capacity = reader.GetInt32(1);
                RoomInfo room = new RoomInfo();
                room.id = id;
                room.capacity = capacity;
                roomList.Add(room);
            }
            return roomList;
        }

        public List<RoomInfo> getFreeRoomList(DateTime begin, DateTime end, int capacity)
        {
            connection.Open();
            OdbcCommand cmd = new OdbcCommand("{call ProcGetFreeRoom(?,?,?)}",connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@begin_time",OdbcType.DateTime);
            cmd.Parameters.Add("@end_time", OdbcType.DateTime);
            cmd.Parameters.Add("@capacity", OdbcType.Int);
            cmd.Parameters["@begin_time"].Value = new DateTime(begin.Ticks - (begin.Ticks % TimeSpan.TicksPerSecond), begin.Kind);
            cmd.Parameters["@end_time"].Value = new DateTime(end.Ticks - (end.Ticks % TimeSpan.TicksPerSecond), end.Kind);
            cmd.Parameters["@capacity"].Value = capacity;


            OdbcDataReader reader = cmd.ExecuteReader();

            List<RoomInfo> info_list = new List<RoomInfo>();
            while (reader.Read())
            {
                int id = reader.GetInt32(0);
                int cap = reader.GetInt32(1);
                RoomInfo info = new RoomInfo();
                info.id = id;
                info.capacity = cap;
                info_list.Add(info);
            }
            connection.Close();
            return info_list;
        }

        public RoomInfo getRoomInfoById(int roomId)
        {
            RoomInfo info = new RoomInfo();
            string strQuery = String.Format("select * from room where id = '{0}'", roomId);
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                info.id = reader.GetInt32(0);
                info.capacity = reader.GetInt32(1);
                return info;
            }
            return null;
        }

        public bool updateRoomInfo(RoomInfo info)
        {
           
            return true;
        }

        public void AddRoomUse(int id, DateTime begin, DateTime end, string user)
        {
            connection.Open();
            string strInsert = String.Format("insert into room_use values({0},'{1}','{2}','{3}')",
                                            id, begin, end,"ohluyao");
            OdbcCommand cmd = new OdbcCommand(strInsert, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                connection.Close();
                throw (new Exception("插入RoomUse失败"));
            }
            connection.Close();
        }
        public void addDevice_use(int id, DateTime from, DateTime to, string user_id)
        {
            connection.Open();
            string strInsert = String.Format("insert into device_use values('{0}','{1}','{2}','{3}')", id, from, to, user_id);
            OdbcCommand cmd = new OdbcCommand(strInsert, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                throw (new Exception("插入device_use失败"));
            }
        }
        public List<DeviceInfo> getFreeDevice(int type_id, DateTime from, DateTime to)
        {
            //string strQuery = String.Format("SELECT * FROM device as D where D.type_id  = '{0}'AND D.id not in (select  T.device_id from device_use as T where (T.from_time >= '{1}'  and T.to_time <= '{2}'or T.from_time <= '{3}' and T.to_time >= '{4}' or T.from_time >= '{5}'  and T.from_time <= '{6}' or T.to_time  >= '{7}'  and T.to_time  <= '{8}' ) ", type_id, from, to,from ,to,from,from,to,to );
            connection.Open();
            OdbcCommand cmd = new OdbcCommand("{call ProcGetFreeDevice(?,?,?)}", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@begin_time", OdbcType.DateTime);
            cmd.Parameters.Add("@end_time", OdbcType.DateTime);
            cmd.Parameters.Add("@type_id", OdbcType.Int);

            cmd.Parameters["@begin_time"].Value = new DateTime(from.Ticks - (from.Ticks % TimeSpan.TicksPerSecond), from.Kind);
            cmd.Parameters["@end_time"].Value = new DateTime(to.Ticks - (to.Ticks % TimeSpan.TicksPerSecond), to.Kind);
            cmd.Parameters["@type_id"].Value = type_id;
            OdbcDataReader reader = cmd.ExecuteReader();
            List<DeviceInfo> devicelist = new List<DeviceInfo>();
            while (reader.Read())
            {
                DeviceInfo thisdevice = new DeviceInfo();
                thisdevice.id = reader.GetInt32(0);
                thisdevice.device_name = reader.GetString(1).Replace(" ", null);
                thisdevice.type_id = reader.GetInt32(2);
                thisdevice.from = from.ToString();
                thisdevice.to = to.ToString();
                devicelist.Add(thisdevice);
            }
            return devicelist;
        }


        

        public bool checkEmployee(string user_id)
        {
            connection.Open();
            string strQuery = String.Format("select id from employee where id = '{0}'",user_id);
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows == false)
                return false;
            else return true;
        }

        public List<QjRecord> getQjRecord(DateTime begin, DateTime end)
        {
            List<QjRecord> record_list = new List<QjRecord>();
            string strQuery = string.Format("select * from qingjia_record");
            connection.Open();
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                DateTime dt_begin, dt_end;
                QjRecord new_record = new QjRecord();
                new_record.emp_name = reader.GetString(1);
                dt_begin = DateTime.Parse(reader.GetString(4));
                new_record.begin_time = dt_begin.ToString();//qingjiashijiancong
                //new_record.end_time = reader.GetDateTime();
                new_record.department = reader.GetString(3);
                new_record.reason = reader.GetString(6);
                if (!reader.IsDBNull(9))//has qingjiatianshu
                {
                    double day = reader.GetDouble(9);
                    new_record.end_time = dt_begin.AddDays(day).ToString();
                }
                else if(!reader.IsDBNull(10))
                {
                    dt_end = DateTime.Parse(reader.GetString(10));
                    new_record.end_time = dt_end.ToString();
                }
                record_list.Add(new_record);
            }
            return record_list;
        }


        //-----------------------------管理设备-------------------------------------
        //添加类型
        public int addDeviceType(String name)
        {
            connection.Open();

            int maxid = 0;
            //get id
            String maxIdStr = "Select max(id) from device_type";
            OdbcCommand cmdmaxid = new OdbcCommand(maxIdStr, connection);
            OdbcDataReader reader = cmdmaxid.ExecuteReader();
            if (reader.Read())
            {
                maxid = reader.GetInt32(0);
            }

            string strInsert = String.Format("insert into device_type values({0},'{1}')",maxid+1, name);
            OdbcCommand cmd = new OdbcCommand(strInsert, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                connection.Close();
                throw (new Exception("插入DeviceType失败"));
            }
            connection.Close();
            return maxid+1;
        }

        //删除类型
        public bool deleteDeviceType(int id)
        {
            connection.Open();
            String deleteStr = String.Format("delete from device_type where id = {0}", id);
            OdbcCommand cmd = new OdbcCommand(deleteStr, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                connection.Close();
                throw (new Exception("删除DeviceType失败"));
            }
            connection.Close();
            return true;
        }

        //获取类型列表
        public List<DeviceTypeInfo> getDeviceTypeList()
        {
            string strQuery = "select * from device_type order by id";
            connection.Open();
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            List<DeviceTypeInfo> deviceType = new List<DeviceTypeInfo>();
            while (reader.Read())
            {
                DeviceTypeInfo typeInfo = new DeviceTypeInfo() ;
                typeInfo.id = reader.GetInt32(0);
                typeInfo.name = reader.GetString(1);

                deviceType.Add(typeInfo);


            }
            connection.Close();
            return deviceType;
        }

        //添加设备
        public bool addDevice(String name, int typeId)
        {

            connection.Open();

            int maxid = 0;
            //get id
            String maxIdStr = "Select max(id) from device";
            OdbcCommand cmdmaxid = new OdbcCommand(maxIdStr, connection);
            OdbcDataReader reader = cmdmaxid.ExecuteReader();
            if (reader.Read())
            {
                maxid = reader.GetInt32(0);
            }

            string strInsert = String.Format("insert into device values({0},'{1}',{2},0)", maxid + 1, name, typeId);
            OdbcCommand cmd = new OdbcCommand(strInsert, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                connection.Close();
                throw (new Exception("插入Device失败"));
            }
            connection.Close();
            return true;
        }

        //删除设备
        public bool deleteDevice(int id)
        {
            connection.Open();
            String deleteStr = String.Format("delete from device where id = {0}", id);
            OdbcCommand cmd = new OdbcCommand(deleteStr, connection);
            if (cmd.ExecuteNonQuery() == 0)
            {
                connection.Close();
                throw (new Exception("删除Device失败"));
            }
            connection.Close();
            return true;
        }

        //获取设备列表
        public List<DeviceInfo> getDeviceList()
        {
            string strQuery = "select * from device,device_type where type_id = device_type.id order by device.id";
            connection.Open();
            OdbcCommand cmd = new OdbcCommand(strQuery, connection);
            OdbcDataReader reader = cmd.ExecuteReader();
            List<DeviceInfo> devices = new List<DeviceInfo>();
            while (reader.Read())
            {
                DeviceInfo deviceInfo = new DeviceInfo();
                deviceInfo.id = reader.GetInt32(0);
                deviceInfo.device_name = reader.GetString(1);
                deviceInfo.type_id = reader.GetInt32(2);
                deviceInfo.type_name = reader.GetString(5);
                devices.Add(deviceInfo);
            }
            connection.Close();
            addDeviceUsageInfo(devices);
            return devices;
        }

        //获取当前设备使用状况
        public void addDeviceUsageInfo(List<DeviceInfo> list)
        {
            DateTime tempTime = DateTime.Now;
            DateTime presentTimeShort = new DateTime(
                tempTime.Year,tempTime.Month,tempTime.Day,tempTime.Hour,
                tempTime.Minute,tempTime.Second
                );
                //new DateTime(2012, 6, 6, 18, 26, 0);
                //
            foreach (DeviceInfo di in list)
            {
                int device_id = di.id;


                connection.Open();
                OdbcCommand cmd = new OdbcCommand("{call ProcGetPresentUsage(?,?)}", connection);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@presentTime", OdbcType.DateTime);
                cmd.Parameters.Add("@device_id", OdbcType.Int);

                cmd.Parameters["@presentTime"].Value = presentTimeShort;
                cmd.Parameters["@device_id"].Value = device_id;
                OdbcDataReader reader = cmd.ExecuteReader();
                if(reader.HasRows)
                {
                    reader.Read();
                    di.from = reader.GetDateTime(1).ToString();
                    di.to = reader.GetDateTime(2).ToString();
                    di.used = true;
                    di.user_name = reader.GetString(5);
                    di.user_id = reader.GetString(4);
                }
                else
                {
                    di.from = "";
                    di.to = "";
                    di.used = false;
                    di.user_name = "";
                    di.user_id = "";
                }


                connection.Close();

            }
            

           

        }

        //获取设备使用历史记录
        public List<DeviceUsage> getDeviceUsageHistory(DateTime from, DateTime to, int type_id)
        {
            connection.Open();
            OdbcCommand cmd = new OdbcCommand("{call ProcGetUsageHistory(?,?,?)}", connection);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@begin_time", OdbcType.DateTime);
            cmd.Parameters.Add("@end_time", OdbcType.DateTime);
            cmd.Parameters.Add("@type_id", OdbcType.Int);

            cmd.Parameters["@begin_time"].Value = new DateTime(from.Ticks - (from.Ticks % TimeSpan.TicksPerSecond), from.Kind);
            cmd.Parameters["@end_time"].Value = new DateTime(to.Ticks - (to.Ticks % TimeSpan.TicksPerSecond), to.Kind);
            cmd.Parameters["@type_id"].Value = type_id;
            OdbcDataReader reader = cmd.ExecuteReader();
            List<DeviceUsage> history = new List<DeviceUsage>();
            while (reader.Read())
            {
                DeviceUsage usage = new DeviceUsage();
                usage.id = reader.GetInt32(0);
                usage.timeFrom = reader.GetDateTime(1).ToString();
                usage.timeTo = reader.GetDateTime(2).ToString();
                usage.userID = reader.GetString(3);
                usage.device_name = reader.GetString(5);
                usage.typeName = reader.GetString(9);
                history.Add(usage);
            }
            connection.Close();
            return history;
        }

    }
}